-- 生成物料变更记录
alter proc proc_czly_GeneMtlModifyRecord(
    @fid int    -- 物料变更单内码
)
as
begin
set nocount on

-- 物料变更记录
declare @id int=ident_current('Z_PAEZ_BD_MtlModifyRecord')+1;

declare @record table (
    FSeq int,
    FID int,
    FMaterialID bigint,
    FMaterialName varchar(255),
    FSpecification varchar(255),
    FPrintUnit varchar(50),
    F_CZ_DRCNO varchar(255),
    FEffectiveDate datetime
)

select row_number() over(order by m.FMaterialID) FSeq, 0 FID, 
    m.FMaterialID, ml.FName FMaterialName, ml.FSpecification, 
    isnull(ul.FName, '') FPrintUnit, isnull(m.F_CZ_DRCNO, '') F_CZ_DRCNO
into #t from (
    select distinct m.FMasterId from PAEZ_t_Cust_WLSJBGSPDEntry we
    inner join T_BD_Material m on m.FMaterialId=we.FNUMBER
    where we.FID=@fid
) mm 
inner join T_BD_MATERIAL m on mm.FMasterId=m.FMasterId
inner join T_BD_MATERIAL_L ml on ml.FMaterialId=m.FMaterialId
left join T_BD_UNIT_L ul on m.F_PAEZ_DYUnitID=ul.FUNITID

declare @mtl_id int = 0
declare @i int = 0
declare @j int = 0
declare @len int = (select count(*) from #t)
while (@i <= @len)
begin 
    set @i += 1

    -- 如果变更物料记录时还不存在该物料，则生成一条该物料的初始记录
    select @mtl_id=FMaterialID from #t where FSeq=@i
    if not exists(select FMaterialID from PAEZ_BD_MtlModifyRecord where FMaterialID=@mtl_id)
    begin
        insert into @record (FSeq, FID, FMaterialID, FMaterialName, FSpecification, FPrintUnit, F_CZ_DRCNO, FEffectiveDate)
        select  @i+@j, 0, @mtl_id, 
            case isnull(we.FFNAME2, '') when '' then ml.FName else we.FFNAME2 end, 
            case isnull(we.FSPECIFICATION2, '') when '' then ml.FSpecification else we.FSPECIFICATION2 end, 
            isnull(ul.FName, ''), 
            case isnull(we.F_CZ_DRCNO2, '') when '' then isnull(m.F_CZ_DRCNO, '') else we.F_CZ_DRCNO2 end,
            '2010-01-01'
        from T_BD_MATERIAL m
        inner join (
            select distinct m.FMasterId, w.FFNAME2, w.FSPECIFICATION2, w.F_CZ_DRCNO2 
            from PAEZ_t_Cust_WLSJBGSPDEntry w
            inner join T_BD_MATERIAL m on m.FMaterialId=w.FNUMBER
            where w.FID=@fid
        ) we on m.FMasterId=we.FMasterId and m.FMaterialId=@mtl_id
        inner join T_BD_MATERIAL_L ml on ml.FMaterialId=m.FMaterialId
        left join T_BD_UNIT_L ul on m.F_PAEZ_DYUnitID=ul.FUNITID

        set @j += 1
    end
    -- 增加变更后的记录
    insert into @record (FSeq, FID, FMaterialID, FMaterialName, FSpecification, FPrintUnit, F_CZ_DRCNO, FEffectiveDate)
    select @i+@j, FID, FMaterialID, FMaterialName, FSpecification, FPrintUnit, F_CZ_DRCNO, getdate()
    from #t where FSeq=@i
end

update @record set FID=@id+FSeq;
select @id=max(FID) from @record;
DBCC CHECKIDENT(Z_PAEZ_BD_MtlModifyRecord, RESEED, @id);

insert into PAEZ_BD_MtlModifyRecord(FID, FMaterialId, FMtlName, FMtlSpecification, FPrintUnit, FRegistrationNo, FEffectiveDate)
select FID, FMaterialID, FMaterialName, FSpecification, FPrintUnit, F_CZ_DRCNO, FEffectiveDate from @record;
drop table #t

end

-- exec proc_czly_GeneMtlModifyRecord @fid=0